﻿using Dapper;
using DapperExtensions;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DapperExtentionCore.Test
{
    //目的支持多个数据库和多个对应不同表
    public static class DatabaseExtensions
    {
        /// <summary>
        /// Dapper扩展方法，执行复杂查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IEnumerable<T> GetList<T>(this IDatabase database, string sql, Dictionary<string, object> parameters)
        {
            DynamicParameters dynamicParameters = new DynamicParameters();
            if (parameters != null && parameters.Count > 0)
            {
                foreach (var parameter in parameters)
                {
                    dynamicParameters.Add(parameter.Key, parameter.Value);
                }
            }
            database.TryOpenConnection();
            return database.Connection.Query<T>(sql, dynamicParameters);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="database"></param>
        /// <param name="sql">待执行sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns></returns>
        public static bool Execute(this IDatabase database, string sql, Dictionary<string, object> parameters = null, int? commandTimeOut = null)
        {
            DynamicParameters dynamicParameters = new DynamicParameters();
            if (parameters != null && parameters.Count > 0)
            {
                foreach (var parameter in parameters)
                {
                    dynamicParameters.Add(parameter.Key, parameter.Value);
                }
            }
            database.TryOpenConnection();
            return database.Connection.Execute(sql: sql, param: dynamicParameters, commandTimeout: commandTimeOut) > 0;
        }

        public static object ExecuteScalar(this IDatabase database, string sql, Dictionary<string, object> parameters = null, int? commandTimeOut = null)
        {
            using (IDbCommand command = database.Connection.CreateCommand())
            {
                command.CommandText = sql;
                if (commandTimeOut.HasValue)
                {
                    command.CommandTimeout = commandTimeOut.Value;
                }
                if (parameters != null && parameters.Count > 0)
                {
                    foreach (var pair in parameters)
                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = pair.Key;
                        parameter.Value = pair.Value;
                        command.Parameters.Add(parameter);
                    }
                }

                return command.ExecuteScalar();
            }
        }

        /// <summary>
        /// Dapper扩展方法，执行存储过程(无返参数)
        /// </summary>
        /// <param name="database"></param>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        public static void ExecuteProcedure(this IDatabase database, string procedureName, Dictionary<string, object> parameters = null, int? commandTimeOut = null)
        {
            DynamicParameters dynamicParameters = new DynamicParameters();
            if (parameters != null && parameters.Count > 0)
            {
                foreach (var parameter in parameters)
                {
                    dynamicParameters.Add(parameter.Key, parameter.Value);
                }
            }
            database.TryOpenConnection();
            database.Connection.Execute(sql: procedureName, param: dynamicParameters, commandType: CommandType.StoredProcedure, commandTimeout: commandTimeOut);
        }

        /// <summary>
        /// 查询取值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="sql">查询字符串</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(this IDatabase database, string sql, object param = null, int? commandTimeout = null)
        {
            database.TryOpenConnection();
            return database.Connection.ExecuteScalar<T>(sql, param, null, commandTimeout, CommandType.Text);
        }

        /// <summary>
        /// 统计记录总数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public static int Count<T>(this IDatabase database, object predicate, bool buffered = false) where T : class
        {
            database.TryOpenConnection();
            return database.Connection.Count<T>(predicate);
        }



        /// <summary>
        /// Dapper扩展方法，执行存储过程(带返回参数)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database"></param>
        /// <param name="procedureName"></param>
        /// <param name="parameters">传入参数</param>
        /// <param name="outParameterName">返回参数的名字</param>
        /// <returns></returns>
        public static T ExecuteProcedure<T>(this IDatabase database, string procedureName, Dictionary<string, object> parameters = null, string outParameterName = null, int? commandTimeOut = null)
        {
            DynamicParameters dynamicParameters = new DynamicParameters();
            if (parameters != null && parameters.Count > 0)
            {
                foreach (var parameter in parameters)
                {
                    dynamicParameters.Add(parameter.Key, parameter.Value);
                }
            }
            database.TryOpenConnection();
            database.Connection.Execute(sql: procedureName, param: dynamicParameters, commandType: CommandType.StoredProcedure, commandTimeout: commandTimeOut);

            return dynamicParameters.Get<T>("outParameterName");
        }

        /// <summary>
        /// 查询取值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="sql">查询字符串</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        //public static T ExecuteScalar<T>(this IDatabase database,string sql, object param = null, int? commandTimeout = null)
        //{
        //    //this.TryOpenConnection();
        //    return database.Connection.ExecuteScalar<T>(sql, param,null, commandTimeout, CommandType.Text);
        //}

        ///处理对应的SQL转换位对应查询和分页处理
        public static IEnumerable<T> GetPage<T>(this IDatabase database, string sql, string columns, string order, int rows, int page, out int count, object param = null, bool buffered = true, int? commandTimeout = null)
        {
            string _sql = string.Empty;
            database.TryOpenConnection();
            count = database.ExecuteScalar<int>(string.Format(@"SELECT COUNT(*) FROM ({0}) as a", sql), param, commandTimeout);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    WITH records as(
                        SELECT TOP {0} {1},ROWNUM=ROW_NUMBER() OVER(ORDER BY {3}) 
                        FROM ({2}) as a)
                    SELECT * FROM records
                    WHERE (ROWNUM BETWEEN {4} AND {5}) ORDER BY ROWNUM ASC",
                    rows * page,
                    columns,
                    sql,
                    order,
                    rows * (page - 1) + 1,
                    rows * page);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) as a ORDER BY {3}", rows.ToString(), columns, sql, order);
            }

            return database.Connection.Query<T>(_sql, param, null, buffered, commandTimeout);
        }

        public static IEnumerable<T> GetPageBySql12<T>(this IDatabase database, string sql, string columns, string order, int rows, int page, out int count, object param = null, bool buffered = true, int? commandTimeout = null)
        {
            string _sql = string.Empty;
            database.TryOpenConnection();
            count = database.ExecuteScalar<int>(string.Format(@"SELECT COUNT(1) FROM ({0}) as a", sql), param, commandTimeout);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    ;WITH _data AS (
                      {2}
                    )
                    SELECT {1} FROM _data 
                    ORDER  BY {3} 
                    OFFSET {0} ROWS 
					FETCH NEXT {4} ROWS ONLY
                    ",
                    rows * (page - 1),
                    columns,
                    sql,
                    order,
                    rows);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) as a ORDER BY {3}", rows.ToString(), columns, sql, order);
            }

            return database.Connection.Query<T>(_sql, param, null, buffered, commandTimeout);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="database"></param>
        /// <param name="sql"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <param name="count"></param>
        /// <param name="param"> 必须为对应的 parm</param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static DataSet GetDataSetByPage(this IDatabase database, string sql, string columns, string order, int rows, int page, out int count, bool buffered = true, int? commandTimeout = null, SqlParameter[] param = null)
        {
            string _sql = string.Empty;
            database.TryOpenConnection();
            count = database.ExecuteScalar<int>(string.Format(@"SELECT COUNT(*) FROM ({0}) as a", sql), param, commandTimeout);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    WITH records as(
                        SELECT TOP {0} {1},ROWNUM=ROW_NUMBER() OVER(ORDER BY {3}) 
                        FROM ({2}) as a)
                    SELECT * FROM records
                    WHERE (ROWNUM BETWEEN {4} AND {5}) ORDER BY ROWNUM ASC",
                    rows * page,
                    columns,
                    sql,
                    order,
                    rows * (page - 1) + 1,
                    rows * page);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) as a ORDER BY {3}", rows.ToString(), columns, sql, order);
            }

            if (param != null)
            {
                SqlParameter[] parameterArray = param as SqlParameter[];
                for (int i = 0; i < parameterArray.Length; i = (int)(i + 1))
                {
                    SqlParameter parameter = parameterArray[i];
                    if (parameter.Value == null)
                    {
                        parameter.Value = (System.DBNull.Value);
                    }
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection connection = database.Connection as SqlConnection;
            using (SqlCommand command = new SqlCommand(_sql, connection))
            {
                adapter.SelectCommand = (command);
                command.CommandType = (CommandType.Text);
                if (param != null)
                {
                    SqlParameter[] parameterArray2 = param as SqlParameter[];
                    for (int j = 0; j < parameterArray2.Length; j = (int)(j + 1))
                    {
                        SqlParameter parameter2 = parameterArray2[j];
                        command.Parameters.Add(parameter2);
                    }
                }
                //输出对应返回的结果集
                //command.Parameters.Add(new SqlParameter("@RowsCount", SqlDbType.Int));
                //command.Parameters["@RowsCount"].Direction = ParameterDirection.Output;
                DataSet set = new DataSet();
                adapter.Fill(set);
                //inttotoals = int.Parse(command.Parameters["@RowsCount"].Value.ToString());
                adapter.SelectCommand.Parameters.Clear();
                return set;
                //return database.Connection.Query<T>(_sql, param, null, buffered, commandTimeout);

            }
        }
        /// <summary>
        /// 扩展对应的 datatable 的分页处理 
        /// </summary>
        /// <param name="database"></param>
        /// <param name="sql"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <param name="count"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByPage(this IDatabase database, string sql, string columns, string order, int rows, int page, out int count, bool buffered = true, int? commandTimeout = null, SqlParameter[] param = null)
        {
            //SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            //conn.ConnectionString = db.Connection.ConnectionString;
            //if (conn.State != ConnectionState.Open)
            //{
            //    conn.Open();
            //}
            //conn = (SqlConnection)db.Connection; 直接使用EF 的链接不用再开一个新的
            string _sql = string.Empty;
            database.TryOpenConnection();
            count = database.ExecuteScalar<int>(string.Format(@"SELECT COUNT(*) FROM ({0}) as a", sql), param, commandTimeout);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    WITH records as(
                        SELECT TOP {0} {1},ROWNUM=ROW_NUMBER() OVER(ORDER BY {3}) 
                        FROM ({2}) as a)
                    SELECT * FROM records
                    WHERE (ROWNUM BETWEEN {4} AND {5}) ORDER BY ROWNUM ASC",
                    rows * page,
                    columns,
                    sql,
                    order,
                    rows * (page - 1) + 1,
                    rows * page);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) as a ORDER BY {3}", rows.ToString(), columns, sql, order);
            }

            if (param != null)
            {
                SqlParameter[] parameterArray = param as SqlParameter[];
                for (int i = 0; i < parameterArray.Length; i = (int)(i + 1))
                {
                    SqlParameter parameter = parameterArray[i];
                    if (parameter.Value == null)
                    {
                        parameter.Value = (System.DBNull.Value);
                    }
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection connection = database.Connection as SqlConnection;
            using (SqlCommand command = new SqlCommand(_sql, connection))
            {
                adapter.SelectCommand = (command);
                command.CommandTimeout = commandTimeout ?? 8000;
                command.CommandType = (CommandType.Text);
                if (param != null)
                {
                    SqlParameter[] parameterArray2 = param as SqlParameter[];
                    for (int j = 0; j < parameterArray2.Length; j = (int)(j + 1))
                    {
                        SqlParameter parameter2 = parameterArray2[j];
                        command.Parameters.Add(parameter2);
                    }
                }
                //输出对应返回的结果集
                //command.Parameters.Add(new SqlParameter("@RowsCount", SqlDbType.Int));
                //command.Parameters["@RowsCount"].Direction = ParameterDirection.Output;
                //DataSet set = new DataSet();
                //adapter.Fill(set);
                DataTable table = new DataTable();
                adapter.Fill(table);
                //inttotoals = int.Parse(command.Parameters["@RowsCount"].Value.ToString());
                adapter.SelectCommand.Parameters.Clear();
                return table;
                //conn = (SqlConnection)database.Connection;
                //SqlCommand cmd = new SqlCommand();

                //长时间执行获取结果集控制
                //cmd.Connection = conn;
                //cmd.CommandText = sql;
                //if (parameters != null && parameters.Length > 0)
                //{
                //    foreach (var item in parameters)
                //    {
                //        cmd.Parameters.Add(item);
                //    }
                //}
                //SqlDataAdapter adapter = new SqlDataAdapter(cmd);


                //conn.Close();//连接需要关闭
                //conn.Dispose();
                //return table;
            }
        }


        public static DataSet GetDataSetByPage12(this IDatabase database, string sql, string columns, string order, int rows, int page, out int count, bool buffered = true, int? commandTimeout = null, object param = null)
        {
            string _sql = string.Empty;
            database.TryOpenConnection();
            count = database.ExecuteScalar<int>(string.Format(@"SELECT COUNT(1) FROM ({0}) as a", sql), param, commandTimeout);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    ;WITH _data AS (
                      {2}
                    )
                    SELECT {1} FROM _data 
                    ORDER  BY {3} 
                    OFFSET {0} ROWS 
					FETCH NEXT {4} ROWS ONLY
                    ",
                    rows * (page - 1),
                    columns,
                    sql,
                    order,
                    rows);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) as a ORDER BY {3}", rows.ToString(), columns, sql, order);
            }
            //调用 dapper的DataReader 处理 

            using (IDataReader reader = database.Connection.ExecuteReader(_sql, param, null, commandTimeout, CommandType.Text))
            {
                DataSet ds = new XDataSet();
                ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });
                if (reader != null)
                {
                    if (!reader.IsClosed) try { reader.Close(); }
                        catch { /* don't spoil the existing exception */ }
                    //reader.Dispose();
                }
                return ds;
            }
            /*
            if (param != null)
            {
                SqlParameter[] parameterArray = param as SqlParameter[];
                for (int i = 0; i < parameterArray.Length; i = (int)(i + 1))
                {
                    SqlParameter parameter = parameterArray[i];
                    if (parameter.Value == null)
                    {
                        parameter.Value = (System.DBNull.Value);
                    }
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection connection = database.Connection as SqlConnection;
            using (SqlCommand command = new SqlCommand(_sql, connection))
            {
                adapter.SelectCommand = (command);
                command.CommandType = (CommandType.Text);
                if (param != null)
                {
                    SqlParameter[] parameterArray2 = param as SqlParameter[];
                    for (int j = 0; j < parameterArray2.Length; j = (int)(j + 1))
                    {
                        SqlParameter parameter2 = parameterArray2[j];
                        command.Parameters.Add(parameter2);
                    }
                }
                //输出对应返回的结果集
                //command.Parameters.Add(new SqlParameter("@RowsCount", SqlDbType.Int));
                //command.Parameters["@RowsCount"].Direction = ParameterDirection.Output;
                DataSet set = new DataSet();
                adapter.Fill(set);
                //inttotoals = int.Parse(command.Parameters["@RowsCount"].Value.ToString());
                adapter.SelectCommand.Parameters.Clear();
                return set;
                //return database.Connection.Query<T>(_sql, param, null, buffered, commandTimeout);

            }
            */
        }
        public static void TryOpenConnection(this IDatabase database)
        {
            if (database.Connection.State == ConnectionState.Closed)
            {
                try { database.Connection.Open(); }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        /// <summary>
        /// 扩展对应的 datatable 的分页处理 
        /// </summary>
        /// <param name="database"></param>
        /// <param name="sql"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <param name="count"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByPage12(this IDatabase database, string sql, string columns, string order, int rows, int page, out int count, object param = null, bool buffered = true, int? commandTimeout = null)
        {
            //SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            //conn.ConnectionString = db.Connection.ConnectionString;
            //if (conn.State != ConnectionState.Open)
            //{
            //    conn.Open();
            //}
            //conn = (SqlConnection)db.Connection; 直接使用EF 的链接不用再开一个新的

            // 使用对应的 参数化获取总数 不用打开新的链接 生成随机数的参数防止重复 或者拼接对应的参数 防止重复 


            string _sql = string.Empty;
            database.TryOpenConnection();
            count = database.Connection.ExecuteScalar<int>(string.Format(@"SELECT COUNT(1) FROM ({0}) as a", sql), param);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    ;WITH _data AS (
                      {2}
                    )
                    SELECT {1} FROM _data 
                    ORDER  BY {3} 
                    OFFSET {0} ROWS 
					FETCH NEXT {4} ROWS ONLY
                    ",
                    rows * (page - 1),
                    columns,
                    sql,
                    order,
                    rows);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) as a ORDER BY {3}", rows.ToString(), columns, sql, order);
            }
            //database.Connection.CreateCommand()
            //调用 dapper的DataReader 处理 
            using (IDataReader reader = database.Connection.ExecuteReader(_sql, param, null, commandTimeout, CommandType.Text))
            {
                //DataTable.Load(IDataReader)
                DataTable dt = new DataTable();
                //ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });
                dt.Load(reader);
                if (reader != null)
                {
                    if (!reader.IsClosed) try { reader.Close(); }
                        catch { /* don't spoil the existing exception */ }
                    //reader.Dispose();
                }
                return dt;
            }
            /*
            if (param != null)
            {
                SqlParameter[] parameterArray = param as SqlParameter[];
                for (int i = 0; i < parameterArray.Length; i = (int)(i + 1))
                {
                    SqlParameter parameter = parameterArray[i];
                    if (parameter.Value == null)
                    {
                        parameter.Value = (System.DBNull.Value);
                    }
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection connection = database.Connection as SqlConnection;
            using (SqlCommand command = new SqlCommand(_sql, connection))
            {
                adapter.SelectCommand = (command);
                command.CommandTimeout = commandTimeout ?? 8000;
                command.CommandType = (CommandType.Text);
                if (param != null)
                {
                    SqlParameter[] parameterArray2 = param as SqlParameter[];
                    for (int j = 0; j < parameterArray2.Length; j = (int)(j + 1))
                    {
                        SqlParameter parameter2 = parameterArray2[j];
                        command.Parameters.Add(parameter2);
                    }
                }
                //输出对应返回的结果集
                //command.Parameters.Add(new SqlParameter("@RowsCount", SqlDbType.Int));
                //command.Parameters["@RowsCount"].Direction = ParameterDirection.Output;
                //DataSet set = new DataSet();
                //adapter.Fill(set);
                DataTable table = new DataTable();
                adapter.Fill(table);
                //inttotoals = int.Parse(command.Parameters["@RowsCount"].Value.ToString());
                adapter.SelectCommand.Parameters.Clear();
                return table;
                //conn = (SqlConnection)database.Connection;
                //SqlCommand cmd = new SqlCommand();

                //长时间执行获取结果集控制
                //cmd.Connection = conn;
                //cmd.CommandText = sql;
                //if (parameters != null && parameters.Length > 0)
                //{
                //    foreach (var item in parameters)
                //    {
                //        cmd.Parameters.Add(item);
                //    }
                //}
                //SqlDataAdapter adapter = new SqlDataAdapter(cmd);


                //conn.Close();//连接需要关闭
                //conn.Dispose();
                //return table;
            }
            */
        }

        /// <summary> 
        /// List转换成DataSet 
        /// </summary> 
        /// <typeparam name="T">类型</typeparam> 
        /// <param name="list">将要转换的List</param> 
        /// <returns></returns> 
        public static DataSet ConvertToDataSet<T>(IList<T> list)
        {
            if (list == null || list.Count <= 0)
            {
                return null;
            }
            DataSet ds = new DataSet();
            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;
            System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
            foreach (T t in list)
            {
                if (t == null)
                {
                    continue;
                }
                row = dt.NewRow();
                for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                {
                    System.Reflection.PropertyInfo pi = myPropertyInfo[i];
                    string name = pi.Name;
                    if (dt.Columns[name] == null)
                    {
                        column = new DataColumn(name, pi.PropertyType);
                        dt.Columns.Add(column);
                    }
                    row[name] = pi.GetValue(t, null);
                }
                dt.Rows.Add(row);
            }
            ds.Tables.Add(dt);
            return ds;
        }

        /// <summary>         
        /// DataSetToList         
        /// </summary>          
        /// <typeparam name="T">转换类型</typeparam>         
        /// <param name="dataSet">数据源</param>         
        /// <param name="tableIndex">需要转换表的索引</param>        
        /// /// <returns>泛型集合</returns>
        public static IList<T> DataSetToList<T>(DataSet dataset, int tableIndex)
        {
            //确认参数有效
            if (dataset == null || dataset.Tables.Count <= 0 || tableIndex < 0)
            {
                return null;
            }
            DataTable dt = dataset.Tables[tableIndex];
            IList<T> list = new List<T>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                //获取对象所有属性
                PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
                //属性和名称相同时则赋值
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    foreach (PropertyInfo info in propertyInfo)
                    {
                        if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
                        {
                            if (dt.Rows[i][j] != DBNull.Value)
                            {
                                info.SetValue(_t, dt.Rows[i][j], null);
                            }
                            else
                            {
                                info.SetValue(_t, null, null);
                            }
                            break;
                        }
                    }
                }
                list.Add(_t);
            }
            return list;
        }
        #region  反射List To DataTable


        /// <summary>  
        /// 将集合类转换成DataTable  
        /// </summary>  
        /// <param name="list">集合</param>  
        /// <returns></returns>  
        public static DataTable ListToDataTable(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }

                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

        #endregion
    }
    /// <summary>
    /// 数据适配器，扩展Fill方法
    /// .NET的DataSet.Load方法，底层调用DataAdapter.Fill(DataTable[], IDataReader, int, int)
    /// Dapper想要返回DataSet，需要重写Load方法，不必传入DataTable[]，因为数组长度不确定
    /// </summary>
    public class XLoadAdapter : DataAdapter
    {
        public XLoadAdapter()
        {
        }

        public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords)
        {
            return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
        }
    }

    /// <summary>
    /// 扩展Load方法
    /// </summary>
    public class XDataSet : DataSet
    {
        public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)
        {
            XLoadAdapter adapter = new XLoadAdapter
            {
                FillLoadOption = loadOption,
                MissingSchemaAction = MissingSchemaAction.AddWithKey
            };
            if (handler != null)
            {
                adapter.FillError += handler;
            }
            adapter.FillFromReader(this, reader, 0, 0);
            if (!reader.IsClosed && !reader.NextResult())
            {
                reader.Close();
            }
        }
    }

}
